package com.jxc.jxc01.hch.dao.Impl;


import com.jxc.jxc01.hch.dao.UserDao;
import com.jxc.jxc01.hch.entity.JDBCConn;
import com.jxc.jxc01.hch.entity.PageHelper;
import com.jxc.jxc01.hch.entity.User;
import jdk.nashorn.api.scripting.ScriptObjectMirror;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDaoImpl implements UserDao {
    /**
     * 查询所有员工数据
     * @return
     */
    @Override
    public PageHelper<User> list(PageHelper<User> pageHelper) {
        List<User> list = new ArrayList<>();
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql =  "select * from user limit ?,?";
            PreparedStatement stat=jdbcConn.connect(sql);
            stat.setInt(1,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat.setInt(2,pageHelper.getPageSize());
            ResultSet rs= stat.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setDepartmentid(rs.getInt("departmentid"));
                user.setUsername(rs.getString("username"));
                user.setName(rs.getString("name"));
                user.setSex(rs.getString("sex"));
                user.setEmail(rs.getString("email"));
                user.setPhone(rs.getString("phone"));
                user.setState(rs.getString("state"));
                String date = rs.getString("Creationtime");
                user.setCreationtime(date);
                user.setAuthority(rs.getString("authority"));
                user.setPassword(rs.getString("password"));
                list.add(user);
            }
            pageHelper.setData(list);
            String sql1="select count(*) from user";
            PreparedStatement stat1 = jdbcConn.connect(sql1);
            ResultSet rs1 = stat1.executeQuery();
            if (rs1.next()){
                int count =rs1.getInt("count(*)");
                pageHelper.setTotal(count);
            }
            rs1.close();
            stat1.close();
            rs.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return pageHelper;
    }

    /**
     * 模糊查询
     * @param pageHelper
     * @return
     */
    @Override
    public PageHelper<User> query(PageHelper<User> pageHelper,String username) {
        List<User> list = new ArrayList<>();
        try {
            //加载驱动类
            JDBCConn jdbcConn = new JDBCConn();
            String sql =  "select * from user where username like ? limit ?,?";
            PreparedStatement stat=jdbcConn.connect(sql);
            stat.setString(1,"%"+username+"%");
            stat.setInt(2,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat.setInt(3,pageHelper.getPageSize());
            ResultSet rs= stat.executeQuery();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setDepartmentid(rs.getInt("departmentid"));
                user.setUsername(rs.getString("username"));
                user.setName(rs.getString("name"));
                user.setSex(rs.getString("sex"));
                user.setEmail(rs.getString("email"));
                user.setPhone(rs.getString("phone"));
                user.setState(rs.getString("state"));
                user.setCreationtime(rs.getString("Creationtime"));
                user.setAuthority(rs.getString("authority"));
                user.setPassword(rs.getString("password"));
                list.add(user);
            }
            pageHelper.setData(list);
            String sql1="select count(*) from user where username like ?";
            PreparedStatement stat1 = jdbcConn.connect(sql1);
            stat1.setString(1,"%"+username+"%");
            ResultSet rs1 = stat1.executeQuery();
            if (rs1.next()){
                int count =rs1.getInt("count(*)");
                pageHelper.setTotal(count);
            }
            rs1.close();
            stat1.close();
            rs.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return pageHelper;
    }

    /**
     * 添加新用户
     * @param user
     * @return
     */
    @Override
    public int increase(User user)  {
        int up=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql ="insert into user(" +
                    "departmentid,username,password,name,sex,email,phone,state,Creationtime,authority)" +
                    "values(?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setInt(1,user.getDepartmentid());
            stat.setString(2,user.getUsername());
            stat.setString(3,user.getPassword());
            stat.setString(4,user.getName());
            stat.setString(5,user.getSex());
            stat.setString(6,user.getEmail());
            stat.setString(7,user.getPhone());
            stat.setString(8,user.getState());
            stat.setString(9,user.getCreationtime());
            stat.setString(10, user.getAuthority());
            up = stat.executeUpdate();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return up;
    }

    /**
     * 修改用户数据
     * @param user
     * @return
     */
    @Override
    public int modify(User user) {
        int up;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql = "update user set" +
                    " departmentid=?,username=?,password=?,name=?,sex=?,email=?,phone=?,state=?" +
                    " where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setInt(1,user.getDepartmentid());
            stat.setString(2,user.getUsername());
            stat.setString(3, user.getPassword());
            stat.setString(4,user.getName());
            stat.setString(5,user.getSex());
            stat.setString(6,user.getEmail());
            stat.setString(7,user.getPhone());
            stat.setString(8,user.getState());
            stat.setInt(9,user.getId());
            up = stat.executeUpdate();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return up;
    }

    @Override
    public int delete(int id) {
        int delete=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql = "delete from user where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setInt(1,id);
            delete= stat.executeUpdate();
            stat.close();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
        return delete;
    }

    @Override
    public int reductionPassword(int id) {
        int reduction=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql = "update user set password=? where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setString(1,"123456");
            stat.setInt(2,id);
            reduction= stat.executeUpdate();
            stat.close();
        }catch (Exception e){
            throw new RuntimeException(e);
        }
        return reduction;
    }

    /**
     * 更改权限
     * @param id
     * @param power
     * @return
     */
    @Override
    public int role(int id,String power) {
        int role=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql = "update user set authority=? where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setString(1,power);
            stat.setInt(2,id);
            role= stat.executeUpdate();
            stat.close();
        }catch (Exception e){
            throw new RuntimeException(e);
        }

        return role;
    }
}
